package com.edu.hbwe.book.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.edu.hbwe.book.dao.OrderDao;
import com.edu.hbwe.book.entity.Order;
import com.edu.hbwe.book.entity.User;
import com.edu.hbwe.book.util.DbUtil;


public class OrderDaolmpl implements OrderDao {

	/**
	 *  查找所有订单
	 * @return
	 */
	public List<Order> findAllOrder() {
		//1.创建sql
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id";
		//2.创建QueryRunner对象
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		//3.返回QueryRunner对象query()方法的查询结果
		try {
			return runner.query(sql, new ResultSetHandler<List<Order>>() {			
				public List<Order> handle(ResultSet rs) throws SQLException {
					//创建订单集合
					List<Order> orders = new ArrayList<Order>();
					//循环遍历订单和用户信息
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));
						orders.add(order);

						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);
					}
					return orders;
				}
			});
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return null;
	}


	/**
	 *  多条件查询
	 *  @return
	 */
	public List<Order> findOrderByManyCondition(String id, String receiverName) {
		//1.创建集合对象
		List<Object> objs = new ArrayList<Object>();
		//2.定义查询sql
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id ";
		//3.根据参数拼接sql语句
		if (id != null && id.trim().length() > 0) {
			sql += " and orders.id=?";
			objs.add(id);
		}
		if (receiverName != null && receiverName.trim().length() > 0) {
			sql += " and receiverName=?";
			objs.add(receiverName);
		}
		sql += " order by orders.user_id";
		//4.创建QueryRunner对象
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		//5.返回QueryRunner对象query方法的执行结果
		try {
			return runner.query(sql, new ResultSetHandler<List<Order>>() {
				public List<Order> handle(ResultSet rs) throws SQLException {
					List<Order> orders = new ArrayList<Order>();
					//循环遍历出订单和用户信息
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs
								.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));
						orders.add(order);
						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);

					}

					return orders;
				}
			}, objs.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	//根据id查看单个id
	public Order findOrderById(String id) {
		String sql = "select * from orders,user where orders.user_id=user.id and orders.id=?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		try {
			return runner.query(sql, new ResultSetHandler<Order>() {
				public Order handle(ResultSet rs) throws SQLException {
					Order order = new Order();
					while (rs.next()) {
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));

						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);
					}
					return order;
				}
			}, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	@Override
	public void addProduct(Order order) throws SQLException {
		// 1.生成Sql语句
		String sql = "insert into orders values(?,?,?,?,?,0,null,?)";
		// 2.生成执行sql语句的QueryRunner,不传递参数
		QueryRunner runner = new QueryRunner();
		// 3.执行update()方法插入数据
		runner.update(DbUtil.getConnection(), sql, order.getId(),
				order.getMoney(), order.getReceiverAddress(), order
				.getReceiverName(), order.getReceiverPhone(), order
				.getUser().getId());
	}


	@Override
	public int updateOrderState(String orderid) {
		String sql = "update orders set paystate=1 where id=?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		try {
			return runner.update(sql, orderid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}


	@Override
	public List<Order> findOrderByUser(User user) {
		String sql = "select * from orders where user_id=?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		try {
			return runner.query(sql, new ResultSetHandler<List<Order>>() {
				public List<Order> handle(ResultSet rs) throws SQLException {
					List<Order> orders = new ArrayList<Order>();
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("id"));
						order.setMoney(rs.getDouble("money"));
						order.setOrdertime(rs.getDate("ordertime"));
						order.setPaystate(rs.getInt("paystate"));
						order.setReceiverAddress(rs.getString("receiverAddress"));
						order.setReceiverName(rs.getString("receiverName"));
						order.setReceiverPhone(rs.getString("receiverPhone"));
						order.setUser(user);
						orders.add(order);
					}
					return orders;
				}
			}, user.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}



	@Override
	public void delOrderById(String id) throws SQLException {
		String sql="delete from orders where id=?";		
		QueryRunner runner = new QueryRunner();		
		runner.update(DbUtil.getConnection(),sql,id);		
	}

}
